﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DataAccess
{
    public class MonHoc_DA
    {
        QLHocSinhDataContext ql = new QLHocSinhDataContext();
        public List<DIEMHOCSINH> Loaddata()
        {
            return ql.DIEMHOCSINHs.ToList();
        }
        public bool Them(string hoten, string lop, string monhoc, string hocky, float d15, float diem1t, float dtb)
        {
            DIEMHOCSINH d = new DIEMHOCSINH();
            d.HOTEN = hoten;
            d.LOP = lop;
            d.MONHOC = monhoc;
            d.HOCKY = hocky;
            d.DIEM15 = d15;
            d.DIEM1T = diem1t;
            d.DIEMTB = dtb;
            try
            {
                ql.DIEMHOCSINHs.InsertOnSubmit(d);
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Sua(string hoten, string mon, string hocky, float d15, float diem1t, float dtb)
        {
            DIEMHOCSINH d = ql.DIEMHOCSINHs.Where(p => p.HOTEN == hoten).Where(o=>o.MONHOC==mon).FirstOrDefault();
            d.HOCKY = hocky;
            d.DIEM15 = d15;
            d.DIEM1T = diem1t;
            d.DIEMTB = dtb;
            try
            {
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Xoa(string hoten, string mon)
        {
            DIEMHOCSINH d = ql.DIEMHOCSINHs.Where(p => p.HOTEN == hoten).Where(o=>o.MONHOC==mon).FirstOrDefault();
            try
            {
                ql.DIEMHOCSINHs.DeleteOnSubmit(d);
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public List<Object> LayHS(string lop)
        {
            var sql = from k in ql.HOCSINHLOPs
                      where k.LOP == lop
                      select new { k.HOTENHS };
            return sql.ToList<Object>();
        }
        public bool KT(string lop, string ten, string mon, string hk)
        {
            int kq = ql.DIEMHOCSINHs.Where(p => p.LOP == lop).Where(o => o.HOTEN == ten).Where(i => i.MONHOC == mon).Where(u => u.HOCKY == hk).Count();
            if (kq != 0)
                return true;
            else
                return false;
        }
        //.........tONG KET MON.....................
        public List<Object> Laymon( string hocky)
        {
            var sql = from k in ql.MONHOCs
                  where k.HOCKY==hocky
                  select new { k.TENMON};
            return sql.ToList<Object>();
        }
        public List<Object> Laymonkchk()
        {
            var sql = from k in ql.MONHOCs
                      select new { k.TENMON };
            return sql.ToList<Object>();
        }
        public List<TONGKETMON> Loaddata(string tenm, string hk)
        {
            return ql.TONGKETMONs.Where(p => p.MONHOC == tenm).Where(o=>o.HOCKY==hk).ToList();
        }
        public List<TONGKETMON> LoadTB()
        {
            return ql.TONGKETMONs.ToList();
        }
        //..............
        public bool ThemTKM(string tenlop, int siso)
        {
            TONGKETMON t = new TONGKETMON();
            t.TENLOP = tenlop;
            t.SISO = siso;
            try
            {
                ql.TONGKETMONs.InsertOnSubmit(t);
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool CapnhatTKM(string tenlop, int siso)
        {
            TONGKETMON t = ql.TONGKETMONs.Where(p => p.TENLOP == tenlop).FirstOrDefault();
            t.SISO = siso;
            try
            {
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool XoaTKM(string tenlop)
        {
            TONGKETMON t = ql.TONGKETMONs.Where(p => p.TENLOP == tenlop).FirstOrDefault();
            try
            {
                ql.TONGKETMONs.DeleteOnSubmit(t);
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Theml2(string tenl, string mon, string hk, int ss, int sld, string tile)
        {
            TONGKETMON t = new TONGKETMON();
            t.TENLOP = tenl;
            t.MONHOC = mon;
            t.HOCKY = hk;
            t.SISO = ss;
            t.SOLUONGDAT = sld;
            t.TILE = tile;
            try
            {
                ql.TONGKETMONs.InsertOnSubmit(t);
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool ktl2(string lop, string mh)
        {
            int k = ql.TONGKETMONs.Where(p => p.TENLOP == lop).Where(o => o.MONHOC == mh).Count();
            if (k != 0)
                return true;
            else
                return false;
        }
        public int ktl3(string lop, string mh, string hk)
        {
            var sql = from k in ql.TONGKETMONs
                      where k.TENLOP == lop && k.MONHOC == mh && k.HOCKY != hk
                      select new { k.HOCKY };
            return sql.Count();
        }
        public int ktl4(string lop, string mh, string hk)
        {
            var sql = from k in ql.TONGKETMONs
                      where k.TENLOP == lop && k.MONHOC == mh && k.HOCKY == hk
                      select new { k.HOCKY };
            return sql.Count();
        }
        //........................
        public bool Capnhatl2(string tenlop,string hk, string mon, int sld, string tile)
        {
            TONGKETMON t = ql.TONGKETMONs.Where(p => p.TENLOP == tenlop).Where(o => o.HOCKY == hk).Where(i => i.MONHOC == mon).FirstOrDefault();
            t.SOLUONGDAT = sld;
            t.TILE = tile;
            try
            {
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public string Laydiemdau(string tenm)
        {
            return ql.MONHOCs.Where(p => p.TENMON == tenm).FirstOrDefault().DIEMDAT.ToString();
        }
        public int SLD(float dtb, string lop, string hk, string mon)
        {
            var sql = from k in ql.DIEMHOCSINHs
                      where k.DIEMTB>=dtb && k.LOP==lop && k.HOCKY==hk && k.MONHOC==mon
                      select new { k.DIEMTB};
            return sql.Count();
        }
        //..............Tổng kết học kỳ...........
        //public int DDD(string lop)
        //{
        //    var sql = from k in ql.DIEMHOCSINHs
        //              where k.DIEMTB >= 5 && k.LOP == lop
        //              select new { k.DIEMTB };
        //    return sql.Count();
        //}
        public bool ThemTKHK(string tenl,string hk, int ss, string tl)
        {
            TONGKETHK t = new TONGKETHK();
            t.TENLOP = tenl;
            t.HOCKY = hk;
            t.SISO = ss;
            t.SOLUONGDAT = 0;
            t.TILE = tl;
            try
            {
                ql.TONGKETHKs.InsertOnSubmit(t);
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public void CapnhatTKHK(string tenl, string hk, int ss, int sld, string tl)
        {
            TONGKETHK t = ql.TONGKETHKs.Where(p => p.TENLOP==tenl).Where(o=>o.HOCKY==hk).FirstOrDefault();
            t.SISO = ss;
            t.SOLUONGDAT = sld;
            t.TILE = tl;
            ql.SubmitChanges();
        }
        public int KTHK1(string lop, string hk)
        {
            var sql = from q in ql.TONGKETHKs
                      where q.TENLOP == lop && q.HOCKY != hk
                      select new { q.HOCKY };
            return sql.Count();
        }
        public int KTHK2(string lop, string hk)
        {
            var sql = from k in ql.TONGKETHKs
                      where k.TENLOP == lop && k.HOCKY == hk
                      select new { k.HOCKY };
            return sql.Count();
        }
        //...........Tinh toán............
        public int Demsomonht()
        {
            var sql = from k in ql.MONHOCs
                      select new { k.TENMON};
            return sql.Count();
        }
        public float TT(string ten, string lop, string hk)
        {
            float tt = 0;
            var sql = from k in ql.DIEMHOCSINHs
                      where k.HOTEN == ten && k.LOP == lop && k.HOCKY == hk
                      select new { k.DIEMTB};
            var row = sql.ToList();
            if (sql.Count() < Demsomonht())
                tt = 0;
            else
            {
                for (int i = 0; i < sql.Count(); i++)
                    tt += float.Parse(row[i].DIEMTB.ToString());
            }
            return tt / Demsomonht();
        }
        public int LaySLD(string lop, string hk)
        {
            string s = ql.TONGKETHKs.Where(p => p.TENLOP == lop).Where(o => o.HOCKY == hk).FirstOrDefault().SOLUONGDAT.ToString();
            return int.Parse(s);
        }
        //........Them mon........
        public bool Themmon(string ten, string hk)
        {
            MONHOC m = new MONHOC();
            m.TENMON = ten;
            m.HOCKY = hk;
            m.DIEMDAT = 5;
            try
            {
                ql.MONHOCs.InsertOnSubmit(m);
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Xoamon(string ten, string hk)
        {
            MONHOC m = ql.MONHOCs.Where(p => p.TENMON == ten).Where(o => o.HOCKY == hk).FirstOrDefault();
            try
            {
                ql.MONHOCs.DeleteOnSubmit(m);
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public List<MONHOC> LoadMH()
        {
            return ql.MONHOCs.ToList();
        }
    }
}
